/*
 * SimpleCatalogBroker.java
 *
 * Created on 13 de agosto de 2008, 04:10 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package flexbrokers;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import listeners.InitApp;

/**
 *
 * @author Alirio
 */
public class SimpleCatalogBroker {
  
  /** Creates a new instance of SimpleCatalogBroker */
  public SimpleCatalogBroker() {
  }
  
  //to do
  public List genericList(String table, String idColumn, String nameColumn, String orderByColumn) {
    java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
    java.sql.Statement stmt;
    List list = new ArrayList();
    try {
      
      stmt = cnn.createStatement();
      java.sql.ResultSet rs = stmt.executeQuery("select " + idColumn + "," + nameColumn + " from " + table + " order by " + orderByColumn);
      while (rs.next()) {
        ConvenientBean bean = new ConvenientBean();
        String id = rs.getString(idColumn);
        String name = rs.getString(nameColumn);
        bean.setBusinessKey(id);
        bean.setName(name);
        bean.setBoth(id + "," + name);
        list.add(bean);
      }
      
      rs.close();
      stmt.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    return list;
  }
  
   public List genericList2(String table, String idColumn, String nameColumn, String orderByColumn) {
    java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
    java.sql.Statement stmt;
    List list = new ArrayList();
    try {
      
      stmt = cnn.createStatement();
      java.sql.ResultSet rs = stmt.executeQuery("select " + idColumn + "," + nameColumn + " from " + table + " order by " + orderByColumn);
      while (rs.next()) {
        ConvenientBean bean = new ConvenientBean();
        String id = String.valueOf(rs.getInt(idColumn));
        String name = rs.getString(nameColumn);
        bean.setBusinessKey(id);
        bean.setName(name);
        bean.setBoth(id + "," + name);
        list.add(bean);
      }
      
      rs.close();
      stmt.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    return list;
  }
  
  public List retrieveBrands() {
    java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
    java.sql.Statement stmt;
    List list = new ArrayList();
    try {
      stmt = cnn.createStatement();
      java.sql.ResultSet rs = stmt.executeQuery("select brand from products group by brand");
      while (rs.next()) {
        ConvenientBean bean = new ConvenientBean();
        String label = rs.getString("brand");
        bean.setBusinessKey(label);
        bean.setName(label);
        list.add(bean);
      }
      
      rs.close();
      stmt.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    return list;
  }
  
  public List retrieveSubordinateSalesmen(String sm) {
    java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
    java.sql.Statement stmt;
    List list = new ArrayList();
    try {
      stmt = cnn.createStatement();
      java.sql.ResultSet rs = stmt.executeQuery("select salesmen_sub.salesman_id_sub, salesmen.name " +
          "from salesmen_sub, salesmen " +
          "where salesmen_sub.salesman_id_sub=salesmen.salesman_id and " +
          "salesmen_sub.salesman_id='" + sm + "' order by salesmen.name");
      while (rs.next()) {
        ConvenientBean bean = new ConvenientBean();
        String id = rs.getString("salesman_id_sub");
        String name = rs.getString("name");
        bean.setBusinessKey(id);
        bean.setName(name);
        bean.setBoth(id + "," + name);
        list.add(bean);
      }
      
      rs.close();
      stmt.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    return list;
  }
  
}
